package cn.com.surker.excel;

import cn.com.surker.report.ExcelStyleUtils;
import cn.com.surker.util.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.math.BigDecimal;
import java.util.*;

/**
 * @Author lucky
 * @create 2023年10月16 10:36
 * @desc
 */
public class ExcelReportUtils {

	/**
	 * 导出含合计适配A4纸通用报表
	 * @param sheetName
	 * @param title
	 * @param columnWidth 列宽
	 * @param totalKeyList 合计数据key值
	 * @return
	 */
	public static XSSFWorkbook exportA4TotalReport(String sheetName, String title, Object[] columns,
												   Integer[] columnWidth, Integer rowHeight,Object[] lastRow,
												   List<String> totalKeyList, List<Map<String, Object>> list) {
		XSSFWorkbook wb = new XSSFWorkbook();
		sheetName = sheetName.replaceAll(":", "_");
		XSSFSheet sheet = wb.createSheet(sheetName);//设置列头宽度
		if (columns == null || list == null || columns.length == 0 || list.size() == 0)
			return null;
		for (int i = 0; i < columnWidth.length; i++) {
			sheet.setColumnWidth(i, columnWidth[i]);
		}
		if (StringUtils.isNull(rowHeight))
			rowHeight = 500;
		sheet.setMargin(Sheet.LeftMargin, 0.2);
		// 右边距
		sheet.setMargin(Sheet.RightMargin, 0.1);
		// 上边距
		sheet.setMargin(Sheet.TopMargin, 0.5);
		// 下边距
		sheet.setMargin(Sheet.BottomMargin, 0.4);
		// 页眉边距
		sheet.setMargin(Sheet.HeaderMargin, 0.2);
		// 页脚边距
		sheet.setMargin(Sheet.FooterMargin, 0);
		//添加表头第1行
		PrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);
		printSetup.setScale((short) 60);
		printSetup.setFitWidth((short)1);
		printSetup.setFitHeight((short)0);
		Footer footer = sheet.getFooter();
		footer.setCenter( "第 &P 页，共 &N 页" );
		//添加表头第1行
		XSSFRow oneRow = sheet.createRow(0);
		oneRow.setHeight(rowHeight.shortValue());
		XSSFRow twoRow = sheet.createRow(1);
		twoRow.setHeight(rowHeight.shortValue());
		//合并列
		CellRangeAddress region = new CellRangeAddress(0, 0, 0, columns.length - 1);//合并表头
		sheet.addMergedRegion(region);
		region = new CellRangeAddress(1, 1, 0, columns.length - 1);//合并表头
		sheet.addMergedRegion(region);
		//指定行高
//		threeRow.setHeight((short) 700);
		XSSFCellStyle twoTileStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, IndexedColors.GREY_25_PERCENT.index, IndexedColors.GREY_25_PERCENT.index,
				IndexedColors.GREY_25_PERCENT.index, IndexedColors.GREY_25_PERCENT.index, null);
		twoTileStyle.setFont(ExcelStyleUtils.hssfFont(wb, 10, "宋体", IndexedColors.BLACK.index, true));
		twoTileStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		twoTileStyle.setAlignment(HorizontalAlignment.LEFT);
		XSSFCellStyle titleStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, IndexedColors.GREY_25_PERCENT.index, IndexedColors.GREY_25_PERCENT.index,
				IndexedColors.GREY_25_PERCENT.index, IndexedColors.GREY_25_PERCENT.index, null);
		titleStyle.setFont(ExcelStyleUtils.hssfFont(wb, 14, "宋体", IndexedColors.BLACK.index, true));
		XSSFCellStyle subTitleStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		subTitleStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());//设置背景色
		subTitleStyle.setFont(ExcelStyleUtils.hssfFont(wb, 10, "宋体", IndexedColors.BLACK.index, true));
		subTitleStyle.setWrapText(true);//自动换行
		subTitleStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());//设置背景色 LIGHT_YELLOW

		XSSFCellStyle valueStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		valueStyle.setFont(ExcelStyleUtils.hssfFont(wb, 10, "宋体", IndexedColors.BLACK.index, null));
		valueStyle.setWrapText(true);//自动换行
		XSSFCellStyle countStyle = ExcelStyleUtils.cellStyle(wb, true, (short)1, true,
				true, true, true, true, true, null);
//		countStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());//设置背景色
//		countStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT);
//		countStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		countStyle.setWrapText(true);//自动换行
		countStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.BLACK.index, true));
		XSSFCellStyle nullStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, false, false, false, false, null);
		nullStyle.setFont(ExcelStyleUtils.hssfFont(wb, 10, "宋体", IndexedColors.BLACK.index, null));
		nullStyle.setWrapText(true);//自动换行

		XSSFCell cell = oneRow.createCell(0);//标题
		cell.setCellStyle(titleStyle);
		cell.setCellValue(sheetName);
		for (int i = 1; i < columns.length; i++) {
			cell = oneRow.createCell(i);//标题
			cell.setCellStyle(titleStyle);
		}
		cell = twoRow.createCell(0);//标题
		cell.setCellStyle(titleStyle);
		cell.setCellValue(title);
		for (int i = 1; i < columns.length; i++) {
			cell = twoRow.createCell(i);//标题
			cell.setCellStyle(countStyle);
		}
		createDetailRow(sheet, 2, rowHeight.shortValue(), subTitleStyle, columns);
		String cellCode = CellUtils.convertCellCode(columns.length);
		if (!StringUtils.isNull(cellCode)) {
			CellRangeAddress c = CellRangeAddress.valueOf("A3:" + cellCode + "3");//筛选列
			sheet.setAutoFilter(c);
		}
		Integer index = 3;
		Map<String, Object> totalMap = new LinkedHashMap<>();
		for (int i = 0; i < list.size(); i++) {
			Map<String, Object> temp = list.get(i);
			Object[] valueObjs = convertValue(temp);
			createDetailRow(sheet, index, rowHeight.shortValue(), valueStyle, valueObjs);
			totalMap = convertTotalValue(totalMap, temp, totalKeyList);
			index++;
		}
		Object[] valueObjs = convertValue(totalMap);
		createDetailRow(sheet, index, rowHeight.shortValue(), valueStyle, valueObjs);
		index++;
		if (lastRow != null && lastRow.length > 0)
			createDetailRow(sheet, index, rowHeight.shortValue(), nullStyle, valueObjs);
		return wb;
	}

	/**
	 * 导出通用适应A4纸报表
	 * @param sheetName
	 * @param title
	 * @param list
	 * @return
	 */
	public static XSSFWorkbook exportA4Report(String sheetName, String title, Object[] columns,Integer[] columnWidth,
											  Integer rowHeight,Object[] lastRow,List<Map<String, Object>> list) {
		XSSFWorkbook wb = new XSSFWorkbook();
		XSSFSheet sheet = wb.createSheet(sheetName);//设置列头宽度
		if (columns == null || list == null || columns.length == 0 || list.size() == 0)
			return null;
		for (int i = 0; i < columnWidth.length; i++) {
			sheet.setColumnWidth(i, columnWidth[i]);
		}
		if (StringUtils.isNull(rowHeight))
			rowHeight = 500;
		sheet.setMargin(Sheet.LeftMargin, 0.2);
		// 右边距
		sheet.setMargin(Sheet.RightMargin, 0.1);
		// 上边距
		sheet.setMargin(Sheet.TopMargin, 0.5);
		// 下边距
		sheet.setMargin(Sheet.BottomMargin, 0.4);
		// 页眉边距
		sheet.setMargin(Sheet.HeaderMargin, 0.2);
		// 页脚边距
		sheet.setMargin(Sheet.FooterMargin, 0);
		//添加表头第1行
		PrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);
		printSetup.setScale((short) 60);
		printSetup.setFitWidth((short)1);
		printSetup.setFitHeight((short)0);
		Footer footer = sheet.getFooter();
		footer.setCenter( "第 &P 页，共 &N 页" );
		sheet.setAutobreaks(false); // 关闭自动分页
		sheet.setFitToPage(true); // 设置适合一页宽度
		//添加表头第1行
		XSSFRow oneRow = sheet.createRow(0);
		oneRow.setHeight(rowHeight.shortValue());
		XSSFRow twoRow = sheet.createRow(1);
		twoRow.setHeight(rowHeight.shortValue());
		//合并列
		CellRangeAddress region = new CellRangeAddress(0, 0, 0, columns.length - 1);//合并表头
		sheet.addMergedRegion(region);
		region = new CellRangeAddress(1, 1, 0, columns.length - 1);//合并表头
		sheet.addMergedRegion(region);
		//指定行高
//		threeRow.setHeight((short) 700);
		XSSFCellStyle twoTileStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, IndexedColors.GREY_25_PERCENT.index, IndexedColors.GREY_25_PERCENT.index,
				IndexedColors.GREY_25_PERCENT.index, IndexedColors.GREY_25_PERCENT.index, null);
		twoTileStyle.setFont(ExcelStyleUtils.hssfFont(wb, 10, "宋体", IndexedColors.BLACK.index, true));
		twoTileStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		twoTileStyle.setAlignment(HorizontalAlignment.LEFT);
		XSSFCellStyle titleStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, IndexedColors.GREY_25_PERCENT.index, IndexedColors.GREY_25_PERCENT.index,
				IndexedColors.GREY_25_PERCENT.index, IndexedColors.GREY_25_PERCENT.index, null);
		titleStyle.setFont(ExcelStyleUtils.hssfFont(wb, 14, "宋体", IndexedColors.BLACK.index, true));
		XSSFCellStyle subTitleStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		subTitleStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());//设置背景色
		subTitleStyle.setFont(ExcelStyleUtils.hssfFont(wb, 10, "宋体", IndexedColors.BLACK.index, true));
		subTitleStyle.setWrapText(true);//自动换行
		subTitleStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());//设置背景色 LIGHT_YELLOW

		XSSFCellStyle valueStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		valueStyle.setFont(ExcelStyleUtils.hssfFont(wb, 10, "宋体", IndexedColors.BLACK.index, null));
		valueStyle.setWrapText(true);//自动换行
		XSSFCellStyle countStyle = ExcelStyleUtils.cellStyle(wb, true, (short)1, true,
				true, true, true, true, true, null);
//		countStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());//设置背景色
		countStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		countStyle.setAlignment(HorizontalAlignment.RIGHT);
		countStyle.setFont(ExcelStyleUtils.hssfFont(wb, 14, "宋体", IndexedColors.BLACK.index, true));
		XSSFCellStyle nullStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, false, false, false, false, null);
		nullStyle.setFont(ExcelStyleUtils.hssfFont(wb, 10, "宋体", IndexedColors.BLACK.index, null));
		nullStyle.setWrapText(true);//自动换行

		XSSFCell cell = oneRow.createCell(0);//标题
		cell.setCellStyle(titleStyle);
		cell.setCellValue(sheetName);
		for (int i = 1; i < columns.length; i++) {
			cell = oneRow.createCell(i);//标题
			cell.setCellStyle(titleStyle);
		}
		cell = twoRow.createCell(0);//标题
		cell.setCellStyle(countStyle);
		cell.setCellValue(title);
		for (int i = 1; i < columns.length; i++) {
			cell = twoRow.createCell(i);//标题
			cell.setCellStyle(countStyle);
		}
		createDetailRow(sheet, 2, rowHeight.shortValue(), subTitleStyle, columns);
		String cellCode = CellUtils.convertCellCode(columns.length);
		if (!StringUtils.isNull(cellCode)) {
			CellRangeAddress c = CellRangeAddress.valueOf("A3:" + cellCode + "3");//筛选列
			sheet.setAutoFilter(c);
		}
		Integer index = 3;
		for (int i = 0; i < list.size(); i++) {
			Map<String, Object> temp = list.get(i);
			Object[] valueObjs = convertValue(temp);
			createDetailRow(sheet, index, rowHeight.shortValue(), valueStyle, valueObjs);
			index++;
		}
		if (lastRow != null && lastRow.length > 0)
			createDetailRow(sheet, index, rowHeight.shortValue(), nullStyle, lastRow);
		return wb;
	}

	/**
	 * 导出喊合计通用报表
	 * @param sheetName
	 * @param title
	 * @param totalKeyList 合计数据key值
	 * @param wb
	 * @return
	 */
	public static XSSFWorkbook exportTotalReport(String sheetName, String title, Object[] columns,
												 List<String> totalKeyList, List<Map<String, Object>> list, XSSFWorkbook wb) {
		sheetName = sheetName.replaceAll(":", "_");
		XSSFSheet sheet = wb.createSheet(sheetName);//设置列头宽度
		if (columns == null || list == null || columns.length == 0 || list.size() == 0)
			return null;
		for (int i = 0; i < columns.length; i++) {
			sheet.setColumnWidth(i, 4400);
		}

		//添加表头第1行
		XSSFRow oneRow = sheet.createRow(0);
		oneRow.setHeight((short) 500);
		XSSFRow twoRow = sheet.createRow(1);
		twoRow.setHeight((short) 500);
		//合并列
		CellRangeAddress region = new CellRangeAddress(0, 0, 0, columns.length - 1);//合并表头
		sheet.addMergedRegion(region);
		region = new CellRangeAddress(1, 1, 0, columns.length - 1);//合并表头
		sheet.addMergedRegion(region);
		//指定行高
//		threeRow.setHeight((short) 700);
		XSSFCellStyle twoTileStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, IndexedColors.GREY_25_PERCENT.index, IndexedColors.GREY_25_PERCENT.index,
				IndexedColors.GREY_25_PERCENT.index, IndexedColors.GREY_25_PERCENT.index, null);
		twoTileStyle.setFont(ExcelStyleUtils.hssfFont(wb, 10, "宋体", IndexedColors.BLACK.index, true));
		twoTileStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		twoTileStyle.setAlignment(HorizontalAlignment.LEFT);
		XSSFCellStyle titleStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, IndexedColors.GREY_25_PERCENT.index, IndexedColors.GREY_25_PERCENT.index,
				IndexedColors.GREY_25_PERCENT.index, IndexedColors.GREY_25_PERCENT.index, null);
		titleStyle.setFont(ExcelStyleUtils.hssfFont(wb, 14, "宋体", IndexedColors.BLACK.index, true));
		XSSFCellStyle subTitleStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		subTitleStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());//设置背景色
		subTitleStyle.setFont(ExcelStyleUtils.hssfFont(wb, 10, "宋体", IndexedColors.BLACK.index, true));
		subTitleStyle.setWrapText(true);//自动换行
		subTitleStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());//设置背景色 LIGHT_YELLOW

		XSSFCellStyle valueStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		valueStyle.setFont(ExcelStyleUtils.hssfFont(wb, 10, "宋体", IndexedColors.BLACK.index, null));
		valueStyle.setWrapText(true);//自动换行
		XSSFCellStyle countStyle = ExcelStyleUtils.cellStyle(wb, true, (short)1, true,
				true, true, true, true, true, null);
//		countStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());//设置背景色
//		countStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT);
//		countStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		countStyle.setWrapText(true);//自动换行
		countStyle.setFont(ExcelStyleUtils.hssfFont(wb, 12, "宋体", IndexedColors.BLACK.index, true));
//		HSSFCellStyle successStyle = ExcelStyleUtils.cellStyle(wb, true, true,
//				true, true, true, true, true, HSSFColor.LIME.index);
//		successStyle.setFont(ExcelStyleUtils.hssfFont(wb, 10, "宋体", IndexedColors.BLACK.index, null));

		XSSFCell cell = oneRow.createCell(0);//标题
		cell.setCellStyle(titleStyle);
		cell.setCellValue(sheetName);
		for (int i = 1; i < columns.length; i++) {
			cell = oneRow.createCell(i);//标题
			cell.setCellStyle(titleStyle);
		}
		cell = twoRow.createCell(0);//标题
		cell.setCellStyle(titleStyle);
		cell.setCellValue(title);
		for (int i = 1; i < columns.length; i++) {
			cell = twoRow.createCell(i);//标题
			cell.setCellStyle(countStyle);
		}
		createDetailRow(sheet, 2, (short) 500, subTitleStyle, columns);
		String cellCode = CellUtils.convertCellCode(columns.length);
		if (!StringUtils.isNull(cellCode)) {
			CellRangeAddress c = CellRangeAddress.valueOf("A3:" + cellCode + "3");//筛选列
			sheet.setAutoFilter(c);
		}
		Integer index = 3;
		Map<String, Object> totalMap = new LinkedHashMap<>();
		for (int i = 0; i < list.size(); i++) {
			Map<String, Object> temp = list.get(i);
			Object[] valueObjs = convertValue(temp);
			createDetailRow(sheet, index, (short) 500, valueStyle, valueObjs);
			totalMap = convertTotalValue(totalMap, temp, totalKeyList);
			index++;
		}
		Object[] valueObjs = convertValue(totalMap);
		createDetailRow(sheet, index, (short) 500, valueStyle, valueObjs);
		return wb;
	}

	/**
	 * 导出通用报表
	 * @param sheetName
	 * @param title
	 * @param list
	 * @param wb
	 * @return
	 */
	public static XSSFWorkbook exportReport(String sheetName, String title, Object[] columns, List<Map<String, Object>> list, XSSFWorkbook wb) {
		XSSFSheet sheet = wb.createSheet(sheetName);//设置列头宽度
		if (columns == null || list == null || columns.length == 0 || list.size() == 0)
			return null;
		for (int i = 0; i < columns.length; i++) {
			sheet.setColumnWidth(i, 4400);
		}

		//添加表头第1行
		XSSFRow oneRow = sheet.createRow(0);
		oneRow.setHeight((short) 500);
		XSSFRow twoRow = sheet.createRow(1);
		twoRow.setHeight((short) 500);
		//合并列
		CellRangeAddress region = new CellRangeAddress(0, 0, 0, columns.length - 1);//合并表头
		sheet.addMergedRegion(region);
		region = new CellRangeAddress(1, 1, 0, columns.length - 1);//合并表头
		sheet.addMergedRegion(region);
		//指定行高
//		threeRow.setHeight((short) 700);
		XSSFCellStyle twoTileStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, IndexedColors.GREY_25_PERCENT.index, IndexedColors.GREY_25_PERCENT.index,
				IndexedColors.GREY_25_PERCENT.index, IndexedColors.GREY_25_PERCENT.index, null);
		twoTileStyle.setFont(ExcelStyleUtils.hssfFont(wb, 10, "宋体", IndexedColors.BLACK.index, true));
		twoTileStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		twoTileStyle.setAlignment(HorizontalAlignment.LEFT);
		XSSFCellStyle titleStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, IndexedColors.GREY_25_PERCENT.index, IndexedColors.GREY_25_PERCENT.index,
				IndexedColors.GREY_25_PERCENT.index, IndexedColors.GREY_25_PERCENT.index, null);
		titleStyle.setFont(ExcelStyleUtils.hssfFont(wb, 14, "宋体", IndexedColors.BLACK.index, true));
		XSSFCellStyle subTitleStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		subTitleStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());//设置背景色
		subTitleStyle.setFont(ExcelStyleUtils.hssfFont(wb, 10, "宋体", IndexedColors.BLACK.index, true));
		subTitleStyle.setWrapText(true);//自动换行
		subTitleStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());//设置背景色 LIGHT_YELLOW

		XSSFCellStyle valueStyle = ExcelStyleUtils.cellStyle(wb, true, true,
				true, true, true, true, true, null);
		valueStyle.setFont(ExcelStyleUtils.hssfFont(wb, 10, "宋体", IndexedColors.BLACK.index, null));
		valueStyle.setWrapText(true);//自动换行
		XSSFCellStyle countStyle = ExcelStyleUtils.cellStyle(wb, true, (short)1, true,
				true, true, true, true, true, null);
//		countStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());//设置背景色
		countStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		countStyle.setAlignment(HorizontalAlignment.RIGHT);
		countStyle.setFont(ExcelStyleUtils.hssfFont(wb, 14, "宋体", IndexedColors.BLACK.index, true));
//		HSSFCellStyle successStyle = ExcelStyleUtils.cellStyle(wb, true, true,
//				true, true, true, true, true, HSSFColor.LIME.index);
//		successStyle.setFont(ExcelStyleUtils.hssfFont(wb, 10, "宋体", IndexedColors.BLACK.index, null));

		XSSFCell cell = oneRow.createCell(0);//标题
		cell.setCellStyle(titleStyle);
		cell.setCellValue(sheetName);
		for (int i = 1; i < columns.length; i++) {
			cell = oneRow.createCell(i);//标题
			cell.setCellStyle(titleStyle);
		}
		cell = twoRow.createCell(0);//标题
		cell.setCellStyle(countStyle);
		cell.setCellValue(title);
		for (int i = 1; i < columns.length; i++) {
			cell = twoRow.createCell(i);//标题
			cell.setCellStyle(countStyle);
		}
		createDetailRow(sheet, 2, (short) 500, subTitleStyle, columns);
		String cellCode = CellUtils.convertCellCode(columns.length);
		if (!StringUtils.isNull(cellCode)) {
			CellRangeAddress c = CellRangeAddress.valueOf("A3:" + cellCode + "3");//筛选列
			sheet.setAutoFilter(c);
		}
		Integer index = 3;
		for (int i = 0; i < list.size(); i++) {
			Map<String, Object> temp = list.get(i);
			Object[] valueObjs = convertValue(temp);
			createDetailRow(sheet, index, (short) 500, valueStyle, valueObjs);
			index++;
		}
		return wb;
	}

	/**
	 * 累计合计值
	 * @param resultMap
	 * @param valueMap
	 * @param keyList
	 * @return
	 */
	private static Map<String, Object> convertTotalValue(Map<String, Object> resultMap, Map<String, Object> valueMap,
														 List<String> keyList) {
		if (resultMap == null || resultMap.keySet().size() == 0)
			resultMap = new LinkedHashMap<>();
		for (String key : valueMap.keySet()) {
			if (keyList.contains(key)) {
				Object d = valueMap.get(key);
				if (resultMap.containsKey(key)) {
					Object o = resultMap.get(key);
					if (o instanceof Double) {
						Double v = StringUtils.convertDouble(o);
						Double r = StringUtils.convertDouble(d) + v;
						resultMap.put(key, r);
					} else if (o instanceof  Integer) {
						Integer v = StringUtils.convertInteger(o);
						Integer r = StringUtils.convertInteger(d) + v;
						resultMap.put(key, r);
					} else {
						resultMap.put(key, "");
					}
				} else {
					if (d instanceof Double) {
						Double v = StringUtils.convertDouble(d);
						resultMap.put(key, v);
					} else if (d instanceof  Integer) {
						Integer v = StringUtils.convertInteger(d);
						resultMap.put(key, v);
					} else {
						resultMap.put(key, "");
					}
				}
			} else {
				resultMap.put(key, "");
			}
		}
		return resultMap;
	}

	/**
	 * 转换填入值
	 * @param map
	 * @return
	 */
	private static Object[] convertValue(Map<String, Object> map) {
		Object[] objs = new Object[map.keySet().size()];
		Integer index = 0;
		for (String key : map.keySet()) {
			objs[index] = map.get(key);
			index++;
		}
		return objs;
	}

	private static void createDetailRow(XSSFSheet sheet,int rowIndex,short rowHeight,XSSFCellStyle cellStyle,Object...cellValues) {
		XSSFRow row = sheet.createRow(rowIndex);
		row.setHeight((short) rowHeight);
		for (int i = 0; i < cellValues.length; i++) {
			XSSFCell cell = row.createCell(i);
			cell.setCellStyle(cellStyle);
			if (StringUtils.isNull(cellValues[i]))
				cell.setCellValue("");
			else {
				if (cellValues[i] instanceof String)
					cell.setCellValue(StringUtils.convertString(cellValues[i]));
				else if (cellValues[i] instanceof Double)
					cell.setCellValue(StringUtils.convertDouble(cellValues[i]));
				else if (cellValues[i] instanceof Integer)
					cell.setCellValue(StringUtils.convertInteger(cellValues[i]));
			}
		}
	}
	private static void createDetailRowV1(XSSFSheet sheet,int rowIndex,short rowHeight,XSSFCellStyle[] cellStyles,Object...cellValues) {
		XSSFRow row = sheet.createRow(rowIndex);
		row.setHeight((short) rowHeight);
		for (int i = 0; i < cellValues.length; i++) {
			XSSFCell cell = row.createCell(i);
			cell.setCellStyle(cellStyles[i]);
			if (StringUtils.isNull(cellValues[i]))
				cell.setCellValue("");
			else {
				if (cellValues[i] instanceof String)
					cell.setCellValue(StringUtils.convertString(cellValues[i]));
				else if (cellValues[i] instanceof Double)
					cell.setCellValue(StringUtils.convertDouble(cellValues[i]));
				else if (cellValues[i] instanceof Integer)
					cell.setCellValue(StringUtils.convertInteger(cellValues[i]));
			}
		}
	}

	/**
	 * 创建明细行
	 * @param sheet
	 * @param rowIndex
	 * @param rowHeight
	 * @param cellValues
	 */
	private static XSSFRow createOutDetailRow(XSSFWorkbook wb,XSSFSheet sheet,int rowIndex,short rowHeight,XSSFCellStyle cellStyle,Object...cellValues){
		if (cellValues == null || cellValues.length < 1){
			return null;
		}
		XSSFRow row = createRow(sheet, rowIndex, rowHeight);
		int curCellIndex = 0;
		for (Object cellValue : cellValues){
			XSSFCell cell = row.createCell(curCellIndex);
			if (cellValue instanceof BigDecimal){
				BigDecimal tcv = (BigDecimal)cellValue;
				BigDecimal bigDecimal = tcv.setScale(2, BigDecimal.ROUND_HALF_UP);
				cell.setCellValue(bigDecimal.toString());
			}else if (cellValue instanceof Double){
				cell.setCellValue((Double)cellValue);
			}else {
				cell.setCellValue(String.valueOf(cellValue));
			}
			if (cellStyle != null){
				cell.setCellStyle(cellStyle);
			}
			curCellIndex++;
		}
		return row;
	}

	/**
	 * 创建行
	 * @param sheet				表
	 * @param currentRowIndex	行索引
	 * @param rowHeight			行高
	 * @return
	 */
	private static XSSFRow createRow(XSSFSheet sheet, int currentRowIndex, short rowHeight) {
		XSSFRow orgRow = sheet.createRow(currentRowIndex);
		orgRow.setHeight(rowHeight);
		return orgRow;
	}

}
